from typing import List

from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> List[str]:
    return [
        """CREATE TABLE IF NOT EXISTS `coffee_categories` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `description` LONGTEXT,
    `beantype_image` VARCHAR(255),
    `sort_order` INT NOT NULL  DEFAULT 0,
    `is_active` BOOL NOT NULL  DEFAULT 1,
    `parent_id` INT,
    CONSTRAINT `fk_coffee_c_coffee_c_0d1a4e87` FOREIGN KEY (`parent_id`) REFERENCES `coffee_categories` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='咖啡豆分类表';
CREATE TABLE IF NOT EXISTS `coffee_products` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `origin_region` VARCHAR(100) NOT NULL,
    `flavor_notes` LONGTEXT,
    `description` LONGTEXT,
    `price` DECIMAL(10,2) NOT NULL,
    `original_price` DECIMAL(10,2),
    `stock` INT NOT NULL  DEFAULT 0,
    `weight` DECIMAL(6,2),
    `image_urls` JSON,
    `is_recommend` BOOL NOT NULL  DEFAULT 0,
    `is_puton` BOOL NOT NULL  DEFAULT 1,
    `sales_count` INT NOT NULL  DEFAULT 0,
    `view_count` INT NOT NULL  DEFAULT 0,
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `category_id` INT NOT NULL,
    CONSTRAINT `fk_coffee_p_coffee_c_c4e8648f` FOREIGN KEY (`category_id`) REFERENCES `coffee_categories` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='咖啡豆产品表';
CREATE TABLE IF NOT EXISTS `merchandise_categories` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL  COMMENT '分类名称',
    `description` LONGTEXT   COMMENT '分类描述',
    `image_url` VARCHAR(255)   COMMENT '分类图片URL',
    `sort_order` INT NOT NULL  COMMENT '排序序号' DEFAULT 0,
    `is_active` BOOL NOT NULL  COMMENT '是否激活' DEFAULT 1,
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `parent_id` INT,
    CONSTRAINT `fk_merchand_merchand_840f324a` FOREIGN KEY (`parent_id`) REFERENCES `merchandise_categories` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='周边商品分类表';
CREATE TABLE IF NOT EXISTS `merchandise_products` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL  COMMENT '商品名称',
    `description` LONGTEXT   COMMENT '商品描述',
    `price` DECIMAL(10,2) NOT NULL  COMMENT '价格',
    `original_price` DECIMAL(10,2)   COMMENT '原价',
    `stock` INT NOT NULL  COMMENT '库存数量' DEFAULT 0,
    `image_urls` JSON   COMMENT '图片URL数组',
    `is_featured` BOOL NOT NULL  COMMENT '是否推荐' DEFAULT 0,
    `is_active` BOOL NOT NULL  COMMENT '是否上架' DEFAULT 1,
    `sales_count` INT NOT NULL  COMMENT '销量' DEFAULT 0,
    `view_count` INT NOT NULL  COMMENT '浏览量' DEFAULT 0,
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `category_id` INT NOT NULL COMMENT '分类ID',
    CONSTRAINT `fk_merchand_merchand_d33ed263` FOREIGN KEY (`category_id`) REFERENCES `merchandise_categories` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='周边商品表';
CREATE TABLE IF NOT EXISTS `product_relations` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `main_product_id` INT NOT NULL  COMMENT '主产品ID',
    `main_product_type` VARCHAR(11) NOT NULL  COMMENT '主产品类型',
    `related_product_id` INT NOT NULL  COMMENT '关联产品ID',
    `related_product_type` VARCHAR(11) NOT NULL  COMMENT '关联产品类型',
    `relation_type` VARCHAR(13) NOT NULL  COMMENT '关联类型' DEFAULT 'complementary',
    `sort_order` INT NOT NULL  COMMENT '排序顺序' DEFAULT 0,
    `is_active` BOOL NOT NULL  COMMENT '是否激活' DEFAULT 1,
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    UNIQUE KEY `uid_product_rel_main_pr_467a07` (`main_product_id`, `main_product_type`, `related_product_id`, `related_product_type`)
) CHARACTER SET utf8mb4 COMMENT='产品关联表';
CREATE TABLE IF NOT EXISTS `product_tags` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL UNIQUE COMMENT '标签名称',
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6)
) CHARACTER SET utf8mb4 COMMENT='产品标签表';
CREATE TABLE IF NOT EXISTS `coffee_product_tags` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `product_id` INT NOT NULL,
    `tag_id` INT NOT NULL,
    UNIQUE KEY `uid_coffee_prod_product_133bd1` (`product_id`, `tag_id`),
    CONSTRAINT `fk_coffee_p_coffee_p_6e7b1769` FOREIGN KEY (`product_id`) REFERENCES `coffee_products` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_coffee_p_product__20767cd3` FOREIGN KEY (`tag_id`) REFERENCES `product_tags` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='咖啡豆-标签关联表';
CREATE TABLE IF NOT EXISTS `merchandise_product_tags` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `product_id` INT NOT NULL,
    `tag_id` INT NOT NULL,
    UNIQUE KEY `uid_merchandise_product_f6aaa9` (`product_id`, `tag_id`),
    CONSTRAINT `fk_merchand_merchand_b89903a5` FOREIGN KEY (`product_id`) REFERENCES `merchandise_products` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_merchand_product__755ff3c2` FOREIGN KEY (`tag_id`) REFERENCES `product_tags` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='周边商品-标签关联表';
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL  COMMENT '密码的哈希值，非明文',
    `head_image` VARCHAR(255),
    `phone` VARCHAR(20),
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `last_login` DATETIME(6),
    `is_active` BOOL NOT NULL  DEFAULT 1
) CHARACTER SET utf8mb4 COMMENT='用户表';
CREATE TABLE IF NOT EXISTS `cart_items` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `quantity` INT NOT NULL  DEFAULT 1,
    `added_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `product_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    CONSTRAINT `fk_cart_ite_coffee_p_eaf99bd2` FOREIGN KEY (`product_id`) REFERENCES `coffee_products` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cart_ite_users_3c009f21` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='购物车表';
CREATE TABLE IF NOT EXISTS `favorites` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `product_id` INT NOT NULL,
    `product_type` VARCHAR(11) NOT NULL  COMMENT '产品类型' DEFAULT 'coffee',
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `user_id` INT NOT NULL,
    UNIQUE KEY `uid_favorites_user_id_f8e8ce` (`user_id`, `product_id`, `product_type`),
    CONSTRAINT `fk_favorite_users_4c63bcf6` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='收藏表';
CREATE TABLE IF NOT EXISTS `user_addresses` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `recipient_name` VARCHAR(50) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `province` VARCHAR(50) NOT NULL,
    `city` VARCHAR(50) NOT NULL,
    `county` VARCHAR(50) NOT NULL,
    `detail_address` VARCHAR(255) NOT NULL,
    `is_default` BOOL NOT NULL  DEFAULT 0,
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `user_id` INT NOT NULL,
    CONSTRAINT `fk_user_add_users_d575dd67` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='用户地址表';
CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `order_number` VARCHAR(50) NOT NULL UNIQUE,
    `total_amount` DECIMAL(10,2) NOT NULL,
    `status` VARCHAR(20) NOT NULL  DEFAULT 'pending',
    `payment_method` VARCHAR(20),
    `payment_status` VARCHAR(20) NOT NULL  DEFAULT 'pending',
    `refund_status` VARCHAR(20) NOT NULL  COMMENT '退款状态' DEFAULT 'none',
    `payment_at` DATETIME(6)   COMMENT '支付成功的时间',
    `remark` LONGTEXT   COMMENT '订单备注',
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `address_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    CONSTRAINT `fk_orders_user_add_0ae852ab` FOREIGN KEY (`address_id`) REFERENCES `user_addresses` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_orders_users_411bb784` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='订单表';
CREATE TABLE IF NOT EXISTS `order_items` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `product_id` INT NOT NULL,
    `product_type` VARCHAR(11) NOT NULL  COMMENT '产品类型' DEFAULT 'coffee',
    `product_name` VARCHAR(100) NOT NULL,
    `product_image` VARCHAR(255),
    `price` DECIMAL(10,2) NOT NULL,
    `quantity` INT NOT NULL,
    `total_price` DECIMAL(10,2) NOT NULL,
    `order_id` INT NOT NULL,
    CONSTRAINT `fk_order_it_orders_b892ad0e` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='订单项表';
CREATE TABLE IF NOT EXISTS `product_reviews` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `product_id` INT NOT NULL,
    `product_type` VARCHAR(11) NOT NULL  COMMENT '产品类型' DEFAULT 'coffee',
    `rating` INT NOT NULL,
    `content` LONGTEXT,
    `image_urls` JSON   COMMENT '用户上传的评论图片URL数组',
    `is_verified` BOOL NOT NULL  COMMENT '是否已验证购买' DEFAULT 0,
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `order_id` INT,
    `user_id` INT NOT NULL,
    CONSTRAINT `fk_product__orders_b1e83261` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_product__users_ff0a87e1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='产品评论表';
CREATE TABLE IF NOT EXISTS `aerich` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `version` VARCHAR(255) NOT NULL,
    `app` VARCHAR(100) NOT NULL,
    `content` JSON NOT NULL
) CHARACTER SET utf8mb4;"""
    ]


async def downgrade(db: BaseDBAsyncClient) -> List[str]:
    return [
        
    ]
